{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "使用2018年数据作为训练集,分别2019年Q1，Q2,Q3,Q4数据作为测试集\n",
    "\n",
    "主要得到：\n",
    "\n",
    "分别计算筛选出的19个特征的PSI"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-02T05:23:10.490708Z",
     "start_time": "2021-07-02T05:23:08.657855Z"
    }
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from sklearn.model_selection import train_test_split\n",
    "import os\n",
    "import tqdm\n",
    "import seaborn as sns\n",
    "from sklearn.linear_model import LogisticRegression\n",
    "from sklearn.metrics import confusion_matrix\n",
    "from sklearn.metrics import accuracy_score\n",
    "from sklearn.metrics import roc_auc_score\n",
    "from sklearn.metrics import roc_curve\n",
    "from sklearn.metrics import cohen_kappa_score\n",
    "from sklearn.metrics import precision_recall_curve\n",
    "from sklearn.model_selection import cross_val_score\n",
    "import variable_bin_methods as vbm\n",
    "import pickle\n",
    "import copy\n",
    "import matplotlib\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 读取数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_Q1 = pd.read_excel('2018年分箱调整所需要的数据.xlsx',header = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_Q21 = pd.read_excel('最终2019年-1分箱调整所需要的数据.xlsx',header = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_Q22 = pd.read_excel('最终2019年-2分箱调整所需要的数据.xlsx',header = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_Q23 = pd.read_excel('最终2019年-3分箱调整所需要的数据.xlsx',header = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_Q24 = pd.read_excel('最终2019年-4分箱调整所需要的数据.xlsx',header = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(260149, 20)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_Q1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((33619, 20), (33595, 20))"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_Q21.shape,data_Q22.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((33792, 20), (33541, 20))"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_Q23.shape,data_Q24.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 测试集定义标签"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-02T05:23:16.709084Z",
     "start_time": "2021-07-02T05:23:16.379012Z"
    }
   },
   "outputs": [],
   "source": [
    "data_X = data_Q1.drop(columns = ['loan_status'])\n",
    "data_y = data_Q1['loan_status']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_X_1 = data_Q21.drop(columns = ['loan_status'])\n",
    "data_y_1 = data_Q21['loan_status']\n",
    "data_X_2= data_Q22.drop(columns = ['loan_status'])\n",
    "data_y_2 = data_Q22['loan_status']\n",
    "data_X_3 = data_Q23.drop(columns = ['loan_status'])\n",
    "data_y_3 = data_Q23['loan_status']\n",
    "data_X_4 = data_Q24.drop(columns = ['loan_status'])\n",
    "data_y_4 = data_Q24['loan_status']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 计算特征的PSI"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "def cal_psi(actual, predict, bins):\n",
    "    actual_min = actual.min()  # 实际中的最小概率\n",
    "    actual_max = actual.max()  # 实际中的最大概率\n",
    "    binlen = (actual_max - actual_min) / bins\n",
    "    cuts = [actual_min + i * binlen for i in range(1, bins)]#设定分组\n",
    "    cuts.insert(0, -float(\"inf\"))\n",
    "    cuts.append(float(\"inf\"))\n",
    "    actual_cuts = np.histogram(actual, bins=cuts)#将actual等距分箱\n",
    "    predict_cuts = np.histogram(predict, bins=cuts)#将predict按actual的分组等距分箱\n",
    "    actual_df = pd.DataFrame(actual_cuts[0],columns=['actual'])\n",
    "    predict_df = pd.DataFrame(predict_cuts[0], columns=['predict'])\n",
    "    psi_df = pd.merge(actual_df,predict_df,right_index=True,left_index=True)\n",
    "    psi_df['actual_rate'] = (psi_df['actual'] + 1) / psi_df['actual'].sum()#计算占比，分子加1，防止计算PSI时分子分母为0\n",
    "    psi_df['predict_rate'] = (psi_df['predict'] + 1) / psi_df['predict'].sum()\n",
    "    psi_df['psi'] = (psi_df['actual_rate'] - psi_df['predict_rate']) * np.log(\n",
    "        psi_df['actual_rate'] / psi_df['predict_rate'])\n",
    "    psi = psi_df['psi'].sum()\n",
    "    return psi, psi_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 连续特征的PSI计算"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2=data_X[\"mths_since_recent_inq\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_21=data_X_1[\"mths_since_recent_inq\"]\n",
    "df_22=data_X_2[\"mths_since_recent_inq\"]\n",
    "df_23=data_X_3[\"mths_since_recent_inq\"]\n",
    "df_24=data_X_4[\"mths_since_recent_inq\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "psi_values21=cal_psi(df_2, df_21,6)\n",
    "psi_values22=cal_psi(df_2, df_22,6)\n",
    "psi_values23=cal_psi(df_2, df_23,6)\n",
    "psi_values24=cal_psi(df_2, df_24,6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0.14882729007417103,\n",
       "    actual  predict  actual_rate  predict_rate       psi\n",
       " 0   99711    11138     0.426510      0.331330  0.024035\n",
       " 1   54635     8116     0.233701      0.241441  0.000252\n",
       " 2   35153     5037     0.150368      0.149856  0.000002\n",
       " 3   23069     3216     0.098680      0.095690  0.000092\n",
       " 4   13485     1875     0.057685      0.055802  0.000063\n",
       " 5    7733     4237     0.033082      0.126060  0.124384)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "psi_values21"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0.14831296315398526,\n",
       "    actual  predict  actual_rate  predict_rate       psi\n",
       " 0   99711    11222     0.426510      0.334068  0.022583\n",
       " 1   54635     8000     0.233701      0.238160  0.000084\n",
       " 2   35153     5099     0.150368      0.151808  0.000014\n",
       " 3   23069     3142     0.098680      0.093556  0.000273\n",
       " 4   13485     1883     0.057685      0.056080  0.000045\n",
       " 5    7733     4249     0.033082      0.126507  0.125313)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "psi_values22"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0.14220165211613184,\n",
       "    actual  predict  actual_rate  predict_rate           psi\n",
       " 0   99711    11371     0.426510      0.336529  2.132082e-02\n",
       " 1   54635     8155     0.233701      0.241359  2.469162e-04\n",
       " 2   35153     5092     0.150368      0.150716  8.038067e-07\n",
       " 3   23069     3162     0.098680      0.093602  2.682685e-04\n",
       " 4   13485     1823     0.057685      0.053977  2.463500e-04\n",
       " 5    7733     4189     0.033082      0.123994  1.201185e-01)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "psi_values23"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0.15001069678123624,\n",
       "    actual  predict  actual_rate  predict_rate       psi\n",
       " 0   99711    11203     0.426510      0.334039  0.022598\n",
       " 1   54635     8138     0.233701      0.242658  0.000337\n",
       " 2   35153     4946     0.150368      0.147491  0.000056\n",
       " 3   23069     3191     0.098680      0.095167  0.000127\n",
       " 4   13485     1800     0.057685      0.053695  0.000286\n",
       " 5    7733     4263     0.033082      0.127128  0.126607)"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "psi_values24"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 离散特征的PSI计算"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1=data_X[\"inq_last_6mths\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_11=data_X_1[\"inq_last_6mths\"]\n",
    "df_12=data_X_2[\"inq_last_6mths\"]\n",
    "df_13=data_X_3[\"inq_last_6mths\"]\n",
    "df_14=data_X_4[\"inq_last_6mths\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1 = df_1.replace(['0.0','1.0','2.0','3.0','4.0','5.0'],[10,11,12,13,14,15])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_11 = df_11.replace(['0.0','1.0','2.0','3.0','4.0','5.0'],[10,11,12,13,14,15])\n",
    "df_12 = df_12.replace(['0.0','1.0','2.0','3.0','4.0','5.0'],[10,11,12,13,14,15])\n",
    "df_13 = df_13.replace(['0.0','1.0','2.0','3.0','4.0','5.0'],[10,11,12,13,14,15])\n",
    "df_14 = df_14.replace(['0.0','1.0','2.0','3.0','4.0','5.0'],[10,11,12,13,14,15])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "psi_values11=cal_psi(df_1, df_11,4)\n",
    "psi_values12=cal_psi(df_1, df_12,4)\n",
    "psi_values13=cal_psi(df_1, df_13,4)\n",
    "psi_values14=cal_psi(df_1, df_14,4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0.00705414510804727,\n",
       "    actual  predict  actual_rate  predict_rate       psi\n",
       " 0  232799    29672     0.894872      0.882626  0.000169\n",
       " 1   20691     2853     0.079539      0.084892  0.000349\n",
       " 2    6191      871     0.023802      0.025938  0.000184\n",
       " 3     468      223     0.001803      0.006663  0.006353)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "psi_values11"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0.006404506917652126,\n",
       "    actual  predict  actual_rate  predict_rate       psi\n",
       " 0  232799    29605     0.894872      0.881262  0.000209\n",
       " 1   20691     2957     0.079539      0.088049  0.000865\n",
       " 2    6191      828     0.023802      0.024676  0.000032\n",
       " 3     468      205     0.001803      0.006132  0.005299)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "psi_values12"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0.00874571139819648,\n",
       "    actual  predict  actual_rate  predict_rate       psi\n",
       " 0  232799    29872     0.894872      0.884026  0.000132\n",
       " 1   20691     2836     0.079539      0.083955  0.000239\n",
       " 2    6191      828     0.023802      0.024532  0.000022\n",
       " 3     468      256     0.001803      0.007605  0.008353)"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "psi_values13"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0.008791462288723169,\n",
       "    actual  predict  actual_rate  predict_rate       psi\n",
       " 0  232799    29672     0.894872      0.884678  0.000117\n",
       " 1   20691     2837     0.079539      0.084613  0.000314\n",
       " 2    6191      778     0.023802      0.023225  0.000014\n",
       " 3     468      254     0.001803      0.007603  0.008347)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "psi_values14"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "288px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
